#! /bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive

${HIVE_HOME} -S -e "
--  每天/每月/每年线上线下以及新老学员的意向用户个数
create table oe_rpt.rpt_date_line_clue_ct(
            a_day string comment'天',
            a_month string comment'月',
            a_year string comment'年',
            line string comment'线上线下',
            clue string comment'新老',
            time_type string comment'日期维度',
            group_type string comment'维度类型',
            count_total bigint comment'意向客户个数'
            )comment '每天/每月/每年线上线下以及新老学员的意向用户个数'
            row format delimited fields terminated by '\t'
            stored as textfile tblproperties ('textfile.compress' = 'snappy');

-- 每天/每月/每年各地区的线上线下以及新老学员的意向用户个数
create table oe_rpt.rpt_date_area_line_clue_ct(
            a_day string comment'天',
            a_month string comment'月',
            a_year string comment'年',
            area string comment'地区',
            line string comment'线上线下',
            clue string comment'新老',
            time_type string comment'日期维度',
            group_type string comment'维度类型',
            count_total bigint comment'意向客户个数'
            )comment '每天/每月/每年各地区的线上线下以及新老学员的意向用户个数'
            row format delimited fields terminated by '\t'
            stored as textfile tblproperties ('textfile.compress' = 'snappy');

-- 每天/每月/每年各学科线上线下以及新老学员的意向用户个数Top10
create table oe_rpt.rpt_date_subject_line_clue_ct(
            a_day string comment'天',
            a_month string comment'月',
            a_year string comment'年',
            subject_name string comment'学科',
            line string comment'线上线下',
            clue string comment'新老',
            time_type string comment'日期维度',
            group_type string comment'维度类型',
            count_total bigint comment'意向客户个数'
            )comment '每天/每月/每年各学科线上线下以及新老学员的意向用户个数Top10'
            row format delimited fields terminated by '\t'
            stored as textfile tblproperties ('textfile.compress' = 'snappy');



  -- 每天/每月/每年各校区线上线下以及新老学员的意向用户个数Top10
create table oe_rpt.rpt_date_school_line_clue_ct(
            a_day string comment'天',
            a_month string comment'月',
            a_year string comment'年',
            school_name string comment'校区',
            line string comment'线上线下',
            clue string comment'新老',
            time_type string comment'日期维度',
            group_type string comment'维度类型',
            count_total bigint comment'意向客户个数'
            )comment '每天/每月/每年各校区线上线下以及新老学员的意向用户个数Top10'
            row format delimited fields terminated by '\t'
            stored as textfile tblproperties ('textfile.compress' = 'snappy');



-- 每天/每月/每年各来源渠道的线上线下以及新老学员的意向用户个数
create table oe_rpt.rpt_date_oc_line_clue_ct(
            a_day string comment'天',
            a_month string comment'月',
            a_year string comment'年',
            origin_channel string comment'来源渠道',
            line string comment'线上线下',
            clue string comment'新老',
            time_type string comment'日期维度',
            group_type string comment'维度类型',
            count_total bigint comment'意向客户个数'
            )comment '每天/每月/每年各来源渠道的线上线下以及新老学员的意向用户个数'
            row format delimited fields terminated by '\t'
            stored as textfile tblproperties ('textfile.compress' = 'snappy');


-- 每天/每月/每年各咨询中心的线上线下以及新老学员的意向用户个数
create table oe_rpt.rpt_date_dp_line_clue_ct(
            a_day string comment'天',
            a_month string comment'月',
            a_year string comment'年',
            name  string comment'咨询中心',
            line string comment'线上线下',
            clue string comment'新老',
            time_type string comment'日期维度',
            group_type string comment'维度类型',
            count_total bigint comment'意向客户个数'
            )comment '每天/每月/每年各咨询中心的线上线下以及新老学员的意向用户个数'
            row format delimited fields terminated by '\t'
            stored as textfile tblproperties ('textfile.compress' = 'snappy');
-- 每天线上线下及新老学员的有效线索个数

create table oe_rpt.rpt_day_line_clue_valid(
             a_day string comment'天',
              line string comment'线上线下',
            clue string comment'新老',
            clue_valid bigint comment'有效线索个数')
             comment '每天线上线下及新老学员的有效线索个数'
            row format delimited fields terminated by '\t'
            stored as textfile tblproperties ('textfile.compress' = 'snappy')  ;

-- 每小时线上线下及新老学员的有效线索转化率=有效线索个数／总线索个数
create table oe_rpt.rpt_day_line_clue_valid_percent(
             a_hour string comment'小时',
              line string comment'线上线下',
            clue string comment'新老',
            rate decimal (38,2) comment'有效线索转换率'
            )comment '每小时线上线下及新老学员的有效线索转化率=有效线索个数／总线索个数'
            row format delimited fields terminated by '\t'
            stored as textfile tblproperties ('textfile.compress' = 'snappy');
"